<?php 

// 学生表
CREATE TABLE `stu` (
  `id` int(11) NOT NULL AUTO_INCREMENT ,
  `name` varchar(128) NOT NULL ,
  `password` varchar(64) NOT NULL,
  `cid` int(3) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='学生表';
CREATE TABLE `class` (
  `id` int(11) NOT NULL AUTO_INCREMENT ,
  `classname` varchar(128) NOT NULL ,
  `comment` varchar(64) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='班级表';
CREATE TABLE `course` (
  `id` int(11) NOT NULL AUTO_INCREMENT ,
  `coursename` varchar(128) NOT NULL ,
  `score` varchar(64) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='课程表';
CREATE TABLE `electives` (
  `uid` int(11) NOT NULL,
  `coid` int(11) NOT NULL 
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='选修中间表';


select sum(t4.score),t1.classname from class t1 left join stu t2 on t1.id=t2.cid left join electives t3 on t2.id=t3.uid left join course t4 on t3.coid=t4.id  group by t1.id;

$sql = "
	SELECT
		coursename,
		name
	FROM
		stu t1 
	LEFT JOIN electives t2 ON t1.id = t2.uid
	LEFT JOIN course t3 ON t2.coid = t3.id
	WHERE t1.id=2
";

检索出和李四都学一样课程的学生

SELECT
		sum(t4.score),
		t1.classname
	FROM
		class t1
	LEFT JOIN stu t2 ON t1.id = t2.cid
	LEFT JOIN electives t3 ON t2.id = t3.uid
	LEFT JOIN course t4 ON t3.coid = t4.id
	GROUP BY
		t1.id;


		select t1.name from stu t1 left join electives t2 on t1.id=t2.uid where t2.coid in (SELECT
		t3.id
	FROM
		stu t1 
	LEFT JOIN electives t2 ON t1.id = t2.uid
	LEFT JOIN course t3 ON t2.coid = t3.id
	WHERE t1.id=2) group by t1.name




		SELECT
			course.id
		FROM
			stu 
		LEFT JOIN electives  ON stu.id = electives.uid
		LEFT JOIN course  ON electives.coid = course.id
		WHERE stu.id=2

















 ?>